package com.typingtutor.ui.compose;

import com.typingtutor.db.DatabaseManager;
import com.typingtutor.util.UUIDUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.*;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.HashMap;
import java.util.Map;

public class ExcelImporter {
    private static volatile ExcelImporter INSTANCE;
    private ExcelImporter() {}
    public static ExcelImporter getInstance() {
        if (INSTANCE == null) {
            synchronized (ExcelImporter.class) {
                if (INSTANCE == null) {
                    INSTANCE = new ExcelImporter();
                }
            }
        }
        return INSTANCE;
    }

    public void importExcel(File file, String parentFolderId) {
        Connection conn = null;
        Savepoint savepoint = null;
        Map<String, String> fieldKeyIdMap = new HashMap<>();
        String folderRandomRulesId = null;
        boolean isFirstImport = false;

        try {
            conn = DatabaseManager.getConnection();
            // 关闭自动提交，开启事务
            conn.setAutoCommit(false);
            savepoint = conn.setSavepoint();

            // 读取 Excel 文件获取默认值
            String randomRange = null;
            String mathField = null;
            try (FileInputStream fis = new FileInputStream(file);
                 Workbook workbook = new XSSFWorkbook(fis)) {
                Sheet firstSheet = workbook.getSheetAt(0);
                randomRange = firstSheet.getSheetName();
                Row headerRow = firstSheet.getRow(0);
                mathField = getCellValueAsString(headerRow.getCell(0));
            }

            // 检查是否已经存在与 parentFolderId 关联的 folder_random_rules 记录
            folderRandomRulesId = getExistingFolderRandomRulesId(conn, parentFolderId);
            if (folderRandomRulesId == null) {
                // 如果不存在，则创建新的 folder_random_rules 记录
                folderRandomRulesId = insertFolderRandomRules(conn, parentFolderId, randomRange, mathField);
                isFirstImport = true;
            }

            try (FileInputStream fis = new FileInputStream(file);
                 Workbook workbook = new XSSFWorkbook(fis)) {

                for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    String folderName = sheet.getSheetName();
                    String folderId = insertFolder(conn, folderName, parentFolderId);

                    Row headerRow = sheet.getRow(0);

                    // 使用修改后的方法处理表头
                    processHeaderRow(conn, headerRow, fieldKeyIdMap);

                    // 插入中间关联表记录，关联 folder_random_rules 和 field_key_info
                    for (String fieldKey : fieldKeyIdMap.keySet()) {
                        String fieldKeyId = fieldKeyIdMap.get(fieldKey);
                        if (!isRelationExists(conn, folderRandomRulesId, fieldKeyId)) {
                            insertFolderRuleFieldKeyRelation(conn, folderRandomRulesId, fieldKeyId);
                        }
                    }

                    // 处理数据行
                    for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                        Row dataRow = sheet.getRow(j);
                        String documentName = getCellValueAsString(dataRow.getCell(0));
                        String documentId = insertDocument(conn, documentName, folderId);

                        for (int k = 0; k < headerRow.getLastCellNum(); k++) {
                            String fieldKey = getCellValueAsString(headerRow.getCell(k));
                            String fieldValue = getCellValueAsString(dataRow.getCell(k));
                            String fieldKeyId = fieldKeyIdMap.get(fieldKey);
                            // 插入字段映射
                            String fieldMappingId = insertFieldMapping(conn, fieldValue, documentId);
                            // 插入中间表关联数据
                            insertFieldKeyMappingRelation(conn, fieldKeyId, fieldMappingId);
                        }
                    }
                }
            }
            // 提交事务
            conn.commit();
        } catch (Exception e) {
            try {
                if (conn != null) {
                    // 回滚到保存点
                    conn.rollback(savepoint);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Excel 导入失败：" + e.getMessage() + "\n");
        } finally {
            try {
                if (conn != null) {
                    // 恢复自动提交模式
                    conn.setAutoCommit(true);
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 修改后的关键代码片段
    private void processHeaderRow(Connection conn, Row headerRow, Map<String, String> fieldKeyIdMap) throws SQLException {
        int columnCount = headerRow.getLastCellNum();
        for (int k = 0; k < columnCount; k++) {
            String fieldKey = getCellValueAsString(headerRow.getCell(k));
            String fieldKeyId = getOrCreateFieldKey(conn, fieldKey);
            fieldKeyIdMap.put(fieldKey, fieldKeyId);
        }
    }

    private String getOrCreateFieldKey(Connection conn, String fieldKey) throws SQLException {
        // 1. 先尝试查询现有字段
        String existingId = getFieldKeyInfoId(conn, fieldKey);
        if (existingId != null) {
            return existingId;
        }

        // 2. 不存在时创建新字段（带唯一约束保护）
        String newId = UUIDUtils.getUUID();
        try {
            insertFieldKeyInfo(conn, newId, fieldKey, false);
            return newId;
        } catch (SQLException e) {
            if (isUniqueConstraintViolation(e)) {
                // 3. 处理并发创建冲突
                return getFieldKeyInfoId(conn, fieldKey);
            }
            throw e;
        }
    }

    // 修改后的字段插入方法（使用事务连接）
    private void insertFieldKeyInfo(Connection conn, String uuid, String fieldKey, boolean isDisplay) throws SQLException {
        String sql = "INSERT INTO field_key_info (id, field_key, `display`) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, uuid);
            pstmt.setString(2, fieldKey);
            pstmt.setBoolean(3, isDisplay);
            pstmt.executeUpdate();
        }
    }

    // 唯一约束冲突检测
    private boolean isUniqueConstraintViolation(SQLException e) {
        return e.getErrorCode() == 23505 || // PostgreSQL
                e.getErrorCode() == 1062;    // MySQL
    }

    private String getExistingFolderRandomRulesId(Connection conn, String parentFolderId) throws SQLException {
        String sql = "SELECT id FROM folder_random_rules WHERE folder_id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, parentFolderId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getString("id");
                }
            }
        }
        return null;
    }

    private String getFieldKeyInfoId(Connection conn, String fieldKey) throws SQLException {
        String sql = "SELECT id FROM field_key_info WHERE field_key = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, fieldKey);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getString("id");
                }
            }
        }
        return null;
    }

    private void insertFieldKeyMappingRelation(Connection conn, String fieldKeyId, String fieldMappingId) throws SQLException {
        String sql = "INSERT INTO field_key_mapping_relation (field_key_id, field_mapping_id) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, fieldKeyId);
            pstmt.setString(2, fieldMappingId);
            pstmt.executeUpdate();
        }
    }

    private String insertFolder(Connection conn, String folderName, String parentFolderId) throws SQLException {
        String id = UUIDUtils.getUUID();
        String sql = "INSERT INTO folders (id, `name`, parent_folder_id) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, id);
            pstmt.setString(2, folderName);
            pstmt.setString(3, parentFolderId);
            pstmt.executeUpdate();
        }
        return id;
    }

    private String insertDocument(Connection conn, String documentName, String folderId) throws SQLException {
        String id = UUIDUtils.getUUID();
        String sql = "INSERT INTO documents (id, `name`, folder_id) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, id);
            pstmt.setString(2, documentName);
            pstmt.setString(3, folderId);
            pstmt.executeUpdate();
        }
        return id;
    }

    private String insertFieldMapping(Connection conn, String fieldValue, String documentId) throws SQLException {
        String id = UUIDUtils.getUUID();
        String sql = "INSERT INTO field_mapping (id, field_value, document_id) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, id);
            pstmt.setString(2, fieldValue);
            pstmt.setString(3, documentId);
            pstmt.executeUpdate();
        }
        return id;
    }

    public void insertFieldKeyInfo(String uuid, String fieldKey, boolean isDisplay) {
        String sql = "INSERT INTO field_key_info (id, field_key, `display`) VALUES (?, ?, ?)";
        try (Connection conn = DatabaseManager.getConnection();) {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, uuid);
            pstmt.setString(2, fieldKey);
            pstmt.setBoolean(3, isDisplay);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private String insertFolderRandomRules(Connection conn, String folderId, String randomRange, String mathField) throws SQLException {
        String id = UUIDUtils.getUUID();
        String sql = "INSERT INTO folder_random_rules (id, random_range, operator_str, math_field, default_enabled, folder_id) " +
                "VALUES (?, ?, '=', ?, false, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, id);
            pstmt.setString(2, randomRange);
            pstmt.setString(3, mathField);
            pstmt.setString(4, folderId);
            pstmt.executeUpdate();
        }
        return id;
    }

    private void insertFolderRuleFieldKeyRelation(Connection conn, String folderRandomRulesId, String fieldKeyInfoId) throws SQLException {
        String sql = "INSERT INTO folder_rule_field_key_relation (folder_random_rules_id, field_key_info_id) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, folderRandomRulesId);
            pstmt.setString(2, fieldKeyInfoId);
            pstmt.executeUpdate();
        }
    }

    private boolean isRelationExists(Connection conn, String folderRandomRulesId, String fieldKeyInfoId) throws SQLException {
        String sql = "SELECT 1 FROM folder_rule_field_key_relation WHERE folder_random_rules_id = ? AND field_key_info_id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, folderRandomRulesId);
            pstmt.setString(2, fieldKeyInfoId);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next();
            }
        }
    }

    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}
